package com.sunjy.demo1.controller;

import cn.afterturn.easypoi.entity.vo.MapExcelConstants;
import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
import cn.afterturn.easypoi.entity.vo.TemplateExcelConstants;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.afterturn.easypoi.view.PoiBaseView;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.sunjy.common.annotation.BeanValid;
import com.sunjy.common.base.BaseController;
import com.sunjy.demo1.excel.Entity.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.MediaType;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URISyntaxException;
import java.util.*;

/**
 * @Auther: sunjy
 * @Date: 2018/10/18 23:00
 * @Description:
 */
@RestController
@RequestMapping("excel")
@Slf4j
public class TestExcelController extends BaseController {

    /**
     * simple直接下载excel
     */
    @BeanValid
    @GetMapping(value="/getSimple")
    public void getSimple() {


    }

    /**
     * 注解式下载excel
     */
    @BeanValid
    @GetMapping(value="/getZhujie",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public void getZhujie(ModelMap map, HttpServletRequest request,
                          HttpServletResponse response) {

        List<UserDetail> list=new ArrayList<>();
        for (int i=0;i<100;i++){
            UserDetail user=new UserDetail();
            user.setId(i);
            if (0==i%2) {
                user.setName("测试"+i);
            }
            user.setSex(i/2+1);
            user.setAge(i%100);
            user.setPhone("12345678900");
            user.setBirthday(new Date());
            user.setDesc("描述"+i);
            user.setImg("img/1.png");
            list.add(user);
        }
        ExportParams params = new ExportParams("所有用户列表", "前一万用户", ExcelType.XSSF);
        params.setFreezeCol(2);
        map.put(NormalExcelConstants.DATA_LIST, list);
        map.put(NormalExcelConstants.CLASS, UserDetail.class);
        map.put(NormalExcelConstants.PARAMS, params);
        map.put(MapExcelConstants.FILE_NAME, "所有用户文件");
        PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);

    }




    /**
     * 注解式读取excel
     */
    @BeanValid
    @GetMapping(value="/readZhujie",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public void readZhujie() {

        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        List<UserDetail> list = ExcelImportUtil.importExcel(
                new File(getWebRootPath("import/所有用户文件.xlsx")),
                UserDetail.class, params);
        System.out.println(JSON.toJSON(list));

    }

    /**
     * 注解式校验读取的excel
     * 过滤无效数据
     */
    @BeanValid
    @GetMapping(value="/checkZhujie",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public void readCheckZhujie() {

        try {
            ImportParams params = new ImportParams();
            params.setTitleRows(1);
            params.setHeadRows(1);
            params.setNeedVerfiy(true);
            params.setVerfiyGroup(new Class[]{ViliGroupOne.class});
            ExcelImportResult<UserDetailCheck> result = ExcelImportUtil.importExcelMore(
                    new File(getWebRootPath("import/所有用户文件check.xlsx")),
                    UserDetailCheck.class, params);
            FileOutputStream fos = new FileOutputStream("E:/excel/ExcelVerifyTest.basetest.xlsx");//将符合要求的记录放在此目录下
            result.getWorkbook().write(fos);
            fos.close();
            for (int i = 0; i < result.getList().size(); i++) {
                System.out.println(ReflectionToStringBuilder.toString(result.getList().get(i)));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    /**
     * template下载excel
     */
    @BeanValid
    @GetMapping(value="/getTemplate")
    public void getTemplate(ModelMap modelMap, HttpServletRequest request,
                            HttpServletResponse response) {
        Map<String, Object> map = new HashMap<String, Object>();
        TemplateExportParams params = new TemplateExportParams(
                "export/foreach.xlsx");
        List<TemplateExcelExportEntity> list = new ArrayList<TemplateExcelExportEntity>();

        for (int i = 0; i < 4; i++) {
            TemplateExcelExportEntity entity = new TemplateExcelExportEntity();
            entity.setIndex(i + 1 + "");
            entity.setAccountType("开源项目");
            entity.setProjectName("EasyPoi " + i + "期");
            entity.setAmountApplied(i * 10000 + "");
            entity.setApprovedAmount((i + 1) * 10000 - 100 + "");
            list.add(entity);
        }
        map.put("entitylist", list);
        map.put("manmark", "1");
        map.put("letest", "12345678");
        map.put("fntest", "12345678.2341234");
        map.put("fdtest", null);
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < 1; i++) {
            Map<String, Object> testMap = new HashMap<String, Object>();

            testMap.put("id", "xman");
            testMap.put("name", "小明" + i);
            testMap.put("sex", "1");
            mapList.add(testMap);
        }
        map.put("maplist", mapList);

        mapList = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < 6; i++) {
            Map<String, Object> testMap = new HashMap<String, Object>();

            testMap.put("si", "xman");
            mapList.add(testMap);
        }
        map.put("sitest", mapList);
        modelMap.put(TemplateExcelConstants.FILE_NAME, "用户信息");
        modelMap.put(TemplateExcelConstants.PARAMS, params);
        modelMap.put(TemplateExcelConstants.MAP_DATA, map);
        PoiBaseView.render(modelMap, request, response,
                TemplateExcelConstants.EASYPOI_TEMPLATE_EXCEL_VIEW);
    }

    private String getWebRootPath(String filePath) {
        try {
            String path = PoiPublicUtil.class.getClassLoader().getResource("").toURI().getPath();
            path = path.replace("WEB-INF/classes/", "");
            path = path.replace("file:/", "");
            return path + filePath;
        } catch (URISyntaxException e) {
            throw new RuntimeException(e);
        }
    }
}
